import numpy as np
import requests
from time import *
import xlsxwriter
import pymysql
import threading
from DBUtils.PooledDB import PooledDB
import pandas as pd

ID = "ww647f8e785f4d83d5"
SECRET = "9lIe12ay42TwCw4glRIDyuzKdWn70jgGjXE0NkcGdYc"

def pool():
    pool = PooledDB(pymysql, 15, host='localhost', user='root', passwd='zcxvbmn', db='weixindb', port=3306)
    return pool

url_get_access_token = "https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=" + ID + "&corpsecret=" + SECRET

#获取access_token#
def get_access_token(ID,SECRET):
    access_token_json = requests.get(url_get_access_token).json()
    while access_token_json['errcode'] != 0:
        get_access_token(ID, SECRET)
    access_token = access_token_json['access_token']
    return access_token

#获取群发任务列表#
url_get_send_msgid_list = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get_groupmsg_list_v2?access_token=" + get_access_token(ID, SECRET)
url_get_send_task_list = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get_groupmsg_task?access_token=" + get_access_token(ID, SECRET)
url_get_send_result_list = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get_groupmsg_send_result?access_token=" + get_access_token(ID, SECRET)
# 获取满足条件的msgid
def get_send_task(start_time, end_time, type):
    msgid_userid_dict_list = []
    publish_inform = {
        "chat_type": type,
        "start_time": start_time,
        "end_time": end_time,
        "filter_type": 0,
        "limit": 100,
        "cursor": ""
    }
    publish_list = requests.post(url_get_send_msgid_list, json=publish_inform)
    publish_content_list = publish_list.json()['group_msg_list']
    for publish_content in publish_content_list:
        msgid = publish_content['msgid']
        i = 1  # i为计数器
        next_cursor = ''
        while i == 1 or next_cursor != '':
            msg_inform = {
                    "msgid": msgid,
                    "limit": 1000,
                    "cursor": next_cursor,
                }
            task_list_full = requests.post(url_get_send_task_list, json=msg_inform)
            task_list = task_list_full.json()["task_list"]
            for task in task_list:
                userid = task['userid']
                msgid_userid_dict = {
                    'msgid': msgid,
                    'userid': userid,
                }
                msgid_userid_dict_list.append(msgid_userid_dict)
            i = i+1
            next_cursor = task_list_full.json()["next_cursor"]
    return msgid_userid_dict_list

def get_send_task_result_task(start_time,end_time,type,start_num,end_num):
    db = pool().connection()
    cursor = db.cursor()
    for msgid_userid_dict in get_send_task(start_time, end_time, type)[start_num:end_num]:
        i = 1  # i为计数器
        next_cursor = ''
        while i == 1 or next_cursor != '':
            send_inform = {
                "msgid": msgid_userid_dict['msgid'],
                "userid": msgid_userid_dict['userid'],
                "limit": 1000,
                "cursor": next_cursor,
            }
            send_result_json = requests.post(url_get_send_result_list, json=send_inform).json()
            tuple_send_result_list = []
            for send_result in send_result_json['send_list']:
                msgid = msgid_userid_dict['msgid']
                external_userid = send_result['external_userid']
                userid = send_result['userid']
                status = repr(send_result['status'])
                try:
                    send_time = repr(send_result['send_time'])
                except:
                    send_time = ''
                tuple_send_result = (msgid, external_userid, userid, status, send_time,)
                tuple_send_result_list.append(tuple_send_result)
            insert_sql = "insert into send_result_list_chongqing(msgid,external_userid,userid,status,send_time) values(%s,%s,%s,%s,%s)"
            cursor.executemany(insert_sql, tuple_send_result_list)
            db.commit()
            i = i + 1
            try:
                next_cursor = send_result_json["next_cursor"]
            except:
                next_cursor = ''
    return

def clean_send_result_and_task_list():
    db = pool().connection()
    cursor = db.cursor()
    clean_send_result_sql = "delete from send_result_list_chongqing"
    clean_send_task_sql = "delete from send_task_list_chongqing"
    cursor.execute(clean_send_result_sql)
    cursor.execute(clean_send_task_sql)
    db.commit()
    return

def get_send_result_threading_task(start_time, end_time, type):
    #前半部分获取task表
    db = pool().connection()
    cursor = db.cursor()
    msgid_userid_dict_list = []
    publish_inform = {
        "chat_type": type,
        "start_time": start_time,
        "end_time": end_time,
        "filter_type": 0,
        "limit": 100,
        "cursor": ""
    }
    publish_list = requests.post(url_get_send_msgid_list, json=publish_inform)
    publish_content_list = publish_list.json()['group_msg_list']
    for publish_content in publish_content_list:
        msgid = publish_content['msgid']
        text_content = publish_content['text']['content'][:20]
        i = 1  # i为计数器
        next_cursor = ''
        while i == 1 or next_cursor != '':
            msg_inform = {
                "msgid": msgid,
                "limit": 1000,
                "cursor": next_cursor,
            }
            task_list_full = requests.post(url_get_send_task_list, json=msg_inform)
            task_list = task_list_full.json()["task_list"]
            tuple_task_list = []
            for task in task_list:
                userid = task['userid']
                status = repr(task['status'])
                try:
                    send_time = repr(task['send_time'])
                except:
                    send_time = ''
                '''
                #用userid去拿员工/姓名/分部/卖场
                url_get_member_inform = "https://qyapi.weixin.qq.com/cgi-bin/user/get?access_token=" + get_access_token(ID, SECRET) + "&userid=" + userid
                name = requests.get(url_get_member_inform).json()['name']
                department_id = requests.get(url_get_member_inform).json()['department'][0]
                # 获取所在部门名称
                get_department_name_sql = "select name from department_list where id=%d" % department_id
                cursor.execute(get_department_name_sql)
                department_name = cursor.fetchone()[0]
                while department_id != 1:
                    get_parentid_sql = "select parentid from department_list where id=%d" % department_id
                    cursor.execute(get_parentid_sql)
                    parentid = cursor.fetchone()[0]
                    get_parent_department_name_sql = "select name from department_list where id=%d" % parentid
                    cursor.execute(get_parent_department_name_sql)
                    parent_department_name = cursor.fetchone()[0]
                    department_name = parent_department_name + "/" + department_name
                    department_id = parentid
                try:
                    branch = department_name[findSubStrIndex("/", department_name, 2) + 1:findSubStrIndex("/", department_name, 3)].replace("'", "")
                except:
                    branch = "/"
                try:
                    store = department_name[findSubStrIndex("/", department_name, 3) + 1:findSubStrIndex("/", department_name, 4)].replace("'", "")
                except:
                    store = "/"
                '''
                tuple_task = (msgid, text_content, userid, status, send_time)
                tuple_task_list.append(tuple_task)
                msgid_userid_dict = {
                    'msgid': msgid,
                    'userid': userid,
                }
                msgid_userid_dict_list.append(msgid_userid_dict)
            insert_sql = "insert into send_task_list_chongqing(msgid, text_content, userid, status, send_time) values(%s,%s,%s,%s,%s)"
            cursor.executemany(insert_sql, tuple_task_list)
            db.commit()
            i = i + 1
            next_cursor = task_list_full.json()["next_cursor"]
    db.close()
    cursor.close()
    list_len = len(msgid_userid_dict_list)
    threads = []
    for n in range(0, list_len, list_len//4):
        start_num = n
        end_num = n+(list_len//4)-1
        task_threading = threading.Thread(target=get_send_task_result_task, args=(start_time, end_time, type, start_num, end_num))
        task_threading.start()
        threads.append(task_threading)
    print("多线程拉取推送任务执行情况中...")
    for task_threading in threads:
        task_threading.join()
    print("拉取推送任务执行情况线程结束")
    return

clean_send_result_and_task_list()
get_send_result_threading_task(1635091200, 1635264000, "single")



###################dataframe####################################
# 不换行打印dataframe
pd.set_option('display.width', 5000)
# 展示所有的列
pd.set_option('display.max_columns', None)

# 将send_task_list表转为dataframe
def get_send_task_df(table_name):
    db = pymysql.connect(host="localhost", user="root", password="zcxvbmn", database="weixindb")
    cursor = db.cursor()
    sql = "select * from %s" % (table_name)
    cursor.execute(sql)
    send_task = cursor.fetchall()
    send_task_df = pd.DataFrame(list(send_task),
                                    columns=['id', 'msgid', 'userid', 'text_content', 'status', 'send_time'])
    db.close()
    return send_task_df

# 将send_result_list表转为dataframe
def get_send_result_df(table_name):
    db = pymysql.connect(host="localhost", user="root", password="zcxvbmn", database="weixindb")
    cursor = db.cursor()
    sql = "select * from %s" % (table_name)
    cursor.execute(sql)
    send_result = cursor.fetchall()
    send_result_df = pd.DataFrame(list(send_result),
                                    columns=['id', 'msgid', 'userid', 'external_userid', 'status', 'send_time'])
    db.close()
    return send_result_df

send_task_df = get_send_task_df("send_task_list_chongqing")
send_result_df = get_send_result_df("send_result_list_chongqing")
print(send_task_df.head())
print(send_result_df.head())

#透视result表
send_result_sum_by_member_df = send_result_df.pivot_table(index=['msgid', 'userid'], columns='status', aggfunc='count', fill_value=0)['external_userid']
print(send_result_sum_by_member_df.head())

#将透视后的result表和task表合并
result_df = pd.merge(send_task_df, send_result_sum_by_member_df, left_on=['msgid', 'userid'], right_index=True)
result_df.columns = ['id', '消息id', '员工编号', '内容', '发送状态', '发送时间', '未发送人数', '已发送人数', '因客户不是好友发送失败人数']
#result_df.loc[result_df['发送状态']==0]='未发送'
#result_df.loc[result_df['发送状态']==2]='已发送'
result_df['发送状态'] = np.where(result_df['发送状态'] == 0, "未发送", "已发送")
#np.where(result_df['发送状态']==2, "已发送")
print(result_df)
with pd.ExcelWriter('D:\推送明细(点对点)_重庆%s.xlsx' % (strftime('%Y%m%d%H%M%S', localtime(time())))) as writer:
    result_df.to_excel(writer, engine='xlsxwriter', sheet_name='点对点')
